using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;

namespace Company.BLL.KDT.SXXK
{
	public partial class BKToKhaiNhap 
	{
        public DataSet getNPL_BKToKhaiNhap(long BangKeHoSoThanhLy_ID)
        {
            try
            {

                string sql = "Select a.SoToKhai,a.MaLoaiHinh,a.NamDangKy,a.NgayDangKy,a.MaHaiQuan,b.MaPhu,b.MaHS,b.TenHang, b.SoLuong, b.DVT_ID,ISNULL(c.Ton,b.SoLuong)as Ton " +
                            "from t_KDT_SXXK_BKToKhaiNhap a inner join t_SXXK_HangMauDich b " +
                            "on a.SoToKhai = b.SoToKhai AND a.MaLoaiHinh = b.MaLoaiHinh " +
                            "AND a.NamDangKy = b.NamDangKy AND a.MaHaiQuan = b.MaHaiQuan " +
                            "left join t_SXXK_ThanhLy_NPLNhapTon c "+
                            "on a.SoToKhai = c.SoToKhai AND c.MaLoaiHinh = c.MaLoaiHinh "+
                            "AND a.NamDangKy = c.NamDangKy AND c.MaHaiQuan = c.MaHaiQuan "+
                            "WHERE BangKeHoSoThanhLy_ID =" + BangKeHoSoThanhLy_ID +
                            " ORDER BY a.NgayThucNhap";
                SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                return db.ExecuteDataSet(cmd);
            }
            catch
            {
                return null;
            }

        }

        public DataSet getBKToKhaiNhapChuaNPL(long BangKeHoSoThanhLy_ID, string maNPL)
        {
            try
            {
                string sql = "Select a.SoToKhai,a.MaLoaiHinh,a.NamDangKy,a.NgayDangKy,a.MaHaiQuan " +
                            "from t_KDT_SXXK_BKToKhaiNhap a inner join t_SXXK_HangMauDich b " +
                            "on a.SoToKhai = b.SoToKhai AND a.MaLoaiHinh = b.MaLoaiHinh " +
                            "AND a.NamDangKy = b.NamDangKy AND a.MaHaiQuan = b.MaHaiQuan " +
                            "WHERE BangKeHoSoThanhLy_ID =" + BangKeHoSoThanhLy_ID +
                            " AND b.MaPhu= '" + maNPL +"'";
                SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                return db.ExecuteDataSet(cmd);
            }
            catch
            {
                return null;
            }

        }
        public DataSet getBKToKhaiNKD(long BangKeHoSoThanhLy_ID)
        {
            try
            {
                string sql = "Select a.SoToKhai,a.MaLoaiHinh,a.NamDangKy,a.NgayDangKy,a.MaHaiQuan,b.MaPhu,b.MaHS,b.TenHang, b.SoLuong, b.DVT_ID, ISNULL(c.Ton,b.SoLuong)as Ton " +
                            "from t_KDT_SXXK_BKToKhaiNhap a inner join t_SXXK_HangMauDich b " +
                            "on a.SoToKhai = b.SoToKhai AND a.MaLoaiHinh = b.MaLoaiHinh " +
                            "AND a.NamDangKy = b.NamDangKy AND a.MaHaiQuan = b.MaHaiQuan " +
                            "left join t_SXXK_ThanhLy_NPLNhapTon c " +
                            "on a.SoToKhai = c.SoToKhai AND c.MaLoaiHinh = c.MaLoaiHinh " +
                            "AND a.NamDangKy = c.NamDangKy AND c.MaHaiQuan = c.MaHaiQuan " +
                            "WHERE BangKeHoSoThanhLy_ID =" + BangKeHoSoThanhLy_ID +
                            " AND a.MaLoaiHinh LIKE 'NKD%'";
                SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                return db.ExecuteDataSet(cmd);
            }
            catch
            {
                return null;
            }

        }
        public decimal getLuongTon(int soToKhai, string maLoaiHinh, string maHaiQuan, short namDangKy, string maNPL)
        {
            try
            {
                string sql = "SELECT Ton FROM t_SXXK_ThanhLy_NPLNhapTon WHERE SoToKhai = @SoToKhai AND MaLoaiHinh = @MaLoaiHinh AND MaHaiQuan =@MaHaiQuan AND NamDangKy = @NamDangKy AND MaNPL = @MaNPL";
                SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();
                DbCommand cmd = db.GetSqlStringCommand(sql);
                db.AddInParameter(cmd, "@SoToKhai", DbType.Int32, soToKhai);
                db.AddInParameter(cmd, "@MaLoaiHinh", DbType.String, maLoaiHinh);
                db.AddInParameter(cmd, "@NamDangKy", DbType.Int16, namDangKy);
                db.AddInParameter(cmd, "@MaHaiQuan", DbType.String, maHaiQuan);
                db.AddInParameter(cmd, "@MaNPL", DbType.String, maNPL);
                decimal ton = Convert.ToDecimal(db.ExecuteScalar(cmd));
                if (ton == 0)
                {
                    sql = "SELECT SoLuong FROM t_SXXK_HangMauDich WHERE SoToKhai = @SoToKhai AND MaLoaiHinh = @MaLoaiHinh AND MaHaiQuan =@MaHaiQuan AND NamDangKy = @NamDangKy AND MaPhu = @MaNPL";
                    cmd = db.GetSqlStringCommand(sql);
                    db.AddInParameter(cmd, "@SoToKhai", DbType.Int32, soToKhai);
                    db.AddInParameter(cmd, "@MaLoaiHinh", DbType.String, maLoaiHinh);
                    db.AddInParameter(cmd, "@NamDangKy", DbType.Int16, namDangKy);
                    db.AddInParameter(cmd, "@MaHaiQuan", DbType.String, maHaiQuan);
                    db.AddInParameter(cmd, "@MaNPL", DbType.String, maNPL);
                    ton = Convert.ToDecimal(db.ExecuteScalar(cmd));
                }            
                return ton;
            }
            catch (Exception ex)
            {
                throw(ex);
            }

        }
	}	
}